/*
 * 创建日期 2009-11-29
 *
 * 成都天和软件公司
 * 电话：028-85425861
 * 传真：028-85425861-8008
 * 邮编：610041 
 * 版权所有
 */
package  org.fantasy.common.db.mdx;

import  org.fantasy.common.util.DateUtil;
import  org.fantasy.common.util.StringUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *  功能说明
 * @author: 王文成
 * @version: 1.0
 * @since 2009-11-29
 */
public class MdxUtils {
    
    public static final String COUNT = "cnt";
    
    private static final Log log = LogFactory.getLog(MdxUtils.class);
    
    /**
     * 取得运行时SQL
     * @return
     */
    public static String getRuntimeSql( String sql ,List values){
        for (int i = 0; i < values.size(); i++) {
            Object value = values.get(i);
            if (value instanceof Number) {
                sql = StringUtils.replaceOnce(sql, "?", value.toString());
            } else if (value instanceof String) {
                sql = StringUtils.replaceOnce(sql, "?", "'" + value.toString() + "'");
            } else if (value instanceof java.util.Date) {
                String dateString = DateUtil.format((java.util.Date) value, "yyyy-MM-dd HH:mm:ss");
                sql = StringUtils.replaceOnce(sql, "?", "to_date('" + dateString + "','YYYY-MM-DD HH24:MI:SS'");
            } else {
                sql = StringUtils.replaceOnce(sql, "?", "'" + String.valueOf(value) + "'");
            }
        }
        return sql;
    }
    
    /**
     * 分页SQL
     * @param pageStart
     * @param pageSize
     * @return
     */
    public static String getPagedSql( String sql , int pageStart , int pageSize){
        int pagedEnd = pageStart + pageSize;
        StringBuffer temp = new StringBuffer(sql.length() + 128);
        temp.append(" SELECT T_2.* FROM (SELECT T_1.*, ROWNUM ROWSEQ FROM ( ");
        temp.append(sql);
        temp.append(" ) T_1 where rownum< " + pagedEnd + ") T_2 WHERE T_2.ROWSEQ >= " + pageStart);
        return temp.toString();
    }
    
    /**
     * 求总数SQL
     * @param pageStart
     * @param pageSize
     * @return
     */
    public static String getCountSql( String sql ){
        StringBuffer temp = new StringBuffer(sql.length() + 128);
        temp.append(" SELECT count(*) as cnt FROM (\n ");
        temp.append( sql);
        temp.append(" \n)");
        return temp.toString();
    }
    
    /**
     * 求总数SQL
     * @param pageStart
     * @param pageSize
     * @return
     */
    public static String getCountSql( String sql , String type ){
        StringBuffer temp = new StringBuffer(sql.length() + 128);
        String countType = StringUtil.isValid(type) ? type : "max(rownum)";
        temp.append(" SELECT NVL(" + countType + ",0) as " + MdxUtils.COUNT + " FROM (\n ");
        temp.append( sql);
        temp.append(" \n)");
        return temp.toString();
    }
    
    /**
     * 关闭所有
     * @param conn
     * @param stmt
     * @param rs
     */
    public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                log.error(ex);
            }
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                log.error(ex);
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.error(ex);
            }
        }
    }
}
